﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using Telerik.Web.UI;
using System.IO;
using KPIS.GERP.GFMS.IBLL;
using KPIS.GERP.GFMS.BLL;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.APP.Reports;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

using KPIS.GERP.GFMS.APP.App_UI.Commons;
using KPIS.GERP.GFMS.DAL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.APP.App_UI.Forms
{
    public partial class SelectReports : System.Web.UI.Page
    {
        MailMerge mailMerge = new MailMerge();
        Main main = new Main();
        IBLL_FIM_SelectReports report = new B_FIM_SelectReports();
        IDAL_FIM_Payments pay = new D_FIM_Payments();
        IDAL_FIM_Receives rec = new D_FIM_Receives();
        IDAL_FIM_Transfers transfer = new D_FIM_Transfers();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                ddlReportName.SelectedIndex = 0;
                ddlReportName_SelectedIndexChanged(null, null);

                txtYear.Text = (DateTime.Today.Year + 543).ToString();
                ddlMonth.SelectedValue = DateTime.Today.Month.ToString();

                txtBgmYear3.Text = main.GetSystemControls().default_bgm_year.ToString();
            }
        }

        protected void ddlReportName_SelectedIndexChanged(object o, RadComboBoxSelectedIndexChangedEventArgs e)
        {
            switch (ddlReportName.SelectedValue)
            {
                case "SumUpPayment":
                    PanelSumUpPayment.Visible = true;
                    PanelSumBorrow.Visible = false;
                    PanelPaymentCheque.Visible = false;
                    PanelMoneyRequestNotPay.Visible = false;
                    PanelVat.Visible = false;
                    PanelCollateral.Visible = false;
                    PanelRemainMoneyRequest.Visible = false;
                    PanelReceiveSummary.Visible = false;
                    LoadBgmSource();
                    rdlReceiveMode.Visible = false;
                    PanelRQPQDN.Visible = false;
                    PanelTax.Visible = false;

                    cmdPrint1.Visible = true;
                    cmdPrint2.Visible = false;
                    chkBankAccount.Visible = false;
                    break;
                case "SumUpReceive":
                    PanelSumUpPayment.Visible = true;
                    PanelSumBorrow.Visible = false;
                    PanelPaymentCheque.Visible = false;
                    PanelMoneyRequestNotPay.Visible = false;
                    PanelRemainMoneyRequest.Visible = false;
                    PanelReceiveSummary.Visible = false;
                    PanelVat.Visible = false;
                    PanelCollateral.Visible = false;
                    LoadBgmSource();
                    LoadddlBank2();
                    rdlReceiveMode.Visible = true;
                    PanelRQPQDN.Visible = false;
                    PanelTax.Visible = false;

                    cmdPrint1.Visible = true;
                    cmdPrint2.Visible = false;
                    chkBankAccount.Visible = false;
                    break;
                case "SumUpBorrow":
                    PanelSumUpPayment.Visible = false;
                    PanelSumBorrow.Visible = true;
                    PanelPaymentCheque.Visible = false;
                    PanelMoneyRequestNotPay.Visible = false;
                    PanelRemainMoneyRequest.Visible = false;
                    PanelReceiveSummary.Visible = false;
                    PanelVat.Visible = false;
                    PanelCollateral.Visible = false;
                    LoadBgmsourceBorrorw();
                    rdlReceiveMode.Visible = false;
                    PanelRQPQDN.Visible = false;
                    PanelTax.Visible = false;

                    txtBgmYear.Text = main.GetSystemControls().default_bgm_year.ToString();
                    LoadDdlProject();
                    LoadDdlEmp();

                    cmdPrint1.Visible = true;
                    cmdPrint2.Visible = false;
                    break;
                case "SumUpPaymentCheque":
                    PanelSumUpPayment.Visible = false;
                    PanelSumBorrow.Visible = false;
                    PanelPaymentCheque.Visible = true;
                    PanelMoneyRequestNotPay.Visible = false;
                    PanelRemainMoneyRequest.Visible = false;
                    PanelReceiveSummary.Visible = false;
                    PanelVat.Visible = false;
                    PanelCollateral.Visible = false;
                    rdlReceiveMode.Visible = false;
                    PanelRQPQDN.Visible = false;
                    PanelTax.Visible = false;

                    txtBgmYear2.Text = main.GetSystemControls().default_bgm_year.ToString();
                    LoadDdlBank();
                    LoadDdlBankAccount();

                    cmdPrint1.Visible = true;
                    cmdPrint2.Visible = false;
                    rblPaymentChequeType_SelectedIndexChanged(null, null);
                    break;
                case "MoneyRequestNotPay":
                    PanelSumUpPayment.Visible = false;
                    PanelSumBorrow.Visible = false;
                    PanelPaymentCheque.Visible = false;
                    PanelMoneyRequestNotPay.Visible = true;
                    PanelRemainMoneyRequest.Visible = false;
                    PanelReceiveSummary.Visible = false;
                    PanelVat.Visible = false;
                    PanelCollateral.Visible = false;
                    LoadBgmsourceMoneyRequestNotPay();
                    rdlReceiveMode.Visible = false;
                    PanelRQPQDN.Visible = false;
                    PanelTax.Visible = false;

                    cmdPrint1.Visible = true;
                    cmdPrint2.Visible = false;
                    break;
                case "Receives":
                    PanelSumUpPayment.Visible = true;
                    PanelSumBorrow.Visible = false;
                    PanelPaymentCheque.Visible = false;
                    PanelMoneyRequestNotPay.Visible = false;
                    PanelRemainMoneyRequest.Visible = false;
                    PanelVat.Visible = false;
                    PanelCollateral.Visible = false;
                    PanelReceiveSummary.Visible = false;
                    LoadBgmSource();
                    LoadddlBank2();
                    rdlReceiveMode.Visible = true;
                    PanelRQPQDN.Visible = false;
                    PanelTax.Visible = false;

                    cmdPrint1.Visible = true;
                    cmdPrint2.Visible = false;
                    chkBankAccount.Visible = false;
                    break;
                case "VatBuy":
                case "VatSale":
                    PanelSumUpPayment.Visible = false;
                    PanelSumBorrow.Visible = false;
                    PanelPaymentCheque.Visible = false;
                    PanelMoneyRequestNotPay.Visible = false;
                    PanelRemainMoneyRequest.Visible = false;
                    PanelVat.Visible = true;
                    PanelCollateral.Visible = false;
                    PanelReceiveSummary.Visible = false;
                    rdlReceiveMode.Visible = false;
                    PanelRQPQDN.Visible = false;
                    LoadBgmSource();
                    PanelTax.Visible = false;

                    cmdPrint1.Visible = true;
                    cmdPrint2.Visible = false;
                    chkBankAccount.Visible = false;
                    break;
                case "Cover":
                    PanelSumUpPayment.Visible = true;
                    PanelSumBorrow.Visible = false;
                    PanelPaymentCheque.Visible = false;
                    PanelMoneyRequestNotPay.Visible = false;
                    PanelRemainMoneyRequest.Visible = false;
                    PanelVat.Visible = false;
                    PanelCollateral.Visible = false;
                    PanelReceiveSummary.Visible = false;
                    LoadBgmSource();
                    LoadddlBank2();
                    rdlReceiveMode.Visible = false;
                    PanelRQPQDN.Visible = false;
                    PanelTax.Visible = false;

                    cmdPrint1.Visible = true;
                    cmdPrint2.Visible = false;
                    chkBankAccount.Visible = false;
                    break;
                case "Collateral":
                    PanelSumUpPayment.Visible = false;
                    PanelSumBorrow.Visible = false;
                    PanelPaymentCheque.Visible = false;
                    PanelMoneyRequestNotPay.Visible = false;
                    PanelRemainMoneyRequest.Visible = false;
                    PanelVat.Visible = false;
                    PanelCollateral.Visible = true;
                    PanelReceiveSummary.Visible = false;
                    LoadBgmSource();
                    LoadddlBank2();
                    rdlReceiveMode.Visible = false;
                    PanelRQPQDN.Visible = false;
                    PanelTax.Visible = false;

                    cmdPrint1.Visible = true;
                    cmdPrint2.Visible = false;
                    chkBankAccount.Visible = false;
                    break;
                case "RemainMoneyRequest":
                    PanelSumUpPayment.Visible = false;
                    PanelSumBorrow.Visible = false;
                    PanelPaymentCheque.Visible = false;
                    PanelMoneyRequestNotPay.Visible = false;
                    PanelRemainMoneyRequest.Visible = true;
                    PanelVat.Visible = false;
                    PanelCollateral.Visible = false;
                    PanelReceiveSummary.Visible = false;
                    PanelRQPQDN.Visible = false;
                    PanelTax.Visible = false;
                    LoadBgmSource();

                    cmdPrint1.Visible = true;
                    cmdPrint2.Visible = false;
                    break;
                case "ReceiveSummary":
                    PanelSumUpPayment.Visible = false;
                    PanelSumBorrow.Visible = false;
                    PanelPaymentCheque.Visible = false;
                    PanelMoneyRequestNotPay.Visible = false;
                    PanelRemainMoneyRequest.Visible = false;
                    PanelVat.Visible = false;
                    PanelCollateral.Visible = false;
                    PanelReceiveSummary.Visible = true;
                    PanelRQPQDN.Visible = false;
                    PanelTax.Visible = false;

                    cmdPrint1.Visible = true;
                    cmdPrint2.Visible = false;
                    break;

                case "RQPQDN":
                    PanelSumUpPayment.Visible = false;
                    PanelSumBorrow.Visible = false;
                    PanelPaymentCheque.Visible = false;
                    PanelMoneyRequestNotPay.Visible = false;
                    PanelRemainMoneyRequest.Visible = false;
                    PanelVat.Visible = false;
                    PanelCollateral.Visible = false;
                    PanelReceiveSummary.Visible = false;
                    PanelRQPQDN.Visible = true;
                    PanelTax.Visible = false;

                    cmdPrint1.Visible = true;
                    cmdPrint2.Visible = false;
                    break;

                case "Tax":
                    PanelSumUpPayment.Visible = false;
                    PanelSumBorrow.Visible = false;
                    PanelPaymentCheque.Visible = false;
                    PanelMoneyRequestNotPay.Visible = false;
                    PanelRemainMoneyRequest.Visible = false;
                    PanelVat.Visible = false;
                    PanelCollateral.Visible = false;
                    PanelReceiveSummary.Visible = false;
                    PanelRQPQDN.Visible = false;
                    PanelTax.Visible = true;
                    txtYear1.Text = (DateTime.Today.Year + 543).ToString();
                    rblTaxType.SelectedValue = "1";
                    rblTaxType_SelectedIndexChanged(null, null);
                    LoadDdlEmp();
                    LoadDdlContact();

                    cmdPrint1.Visible = true;
                    cmdPrint2.Visible = false;
                    break;
                default:
                    PanelSumUpPayment.Visible = false;
                    PanelSumBorrow.Visible = false;
                    PanelPaymentCheque.Visible = false;
                    PanelMoneyRequestNotPay.Visible = false;
                    PanelRemainMoneyRequest.Visible = false;
                    PanelVat.Visible = false;
                    PanelCollateral.Visible = false;
                    rdlReceiveMode.Visible = false;
                    PanelReceiveSummary.Visible = false;
                    PanelRQPQDN.Visible = false;
                    PanelTax.Visible = false;

                    cmdPrint1.Visible = false;
                    cmdPrint2.Visible = false;
                    break;
            }
        }

        protected void LoadDdlProject()
        {
            IBLL_BGM_Project bll = new B_BGM_Project();

            ddlProject.DataSource = bll.GetList(txtBgmYear.Text, null, null);
            ddlProject.DataBind();

            ddlProject.Items.Insert(0, new RadComboBoxItem("--เลือกทั้งหมด--", ""));
        }

        protected void LoadDdlEmp()
        {
            IBLL_HRM_PS_Employees bll = new B_HRM_PS_Employees();

            ddlEmp.DataSource = bll.GetList(null, null, null, "");
            ddlEmp.DataBind();
            ddlEmpSeq.DataSource = bll.GetList(null, null, null, "");
            ddlEmpSeq.DataBind();

            ddlEmp.Items.Insert(0, new RadComboBoxItem("--เลือกทั้งหมด--", ""));
            ddlEmpSeq.Items.Insert(0, new RadComboBoxItem("--เลือกทั้งหมด--", ""));
        }

        protected void LoadDdlContact()
        {
            IBLL_FIM_MAS_Contacts bll = new B_FIM_MAS_Contacts();

            ddlContactSeq.DataSource = bll.GetList(null);
            ddlContactSeq.DataBind();

            ddlContactSeq.Items.Insert(0, new RadComboBoxItem("--เลือกทั้งหมด--", ""));
        }

        public IList<M_FIM_MAS_Banks> LoadBankList()
        {
            IBLL_FIM_MAS_Banks bll = new B_FIM_MAS_Banks();

            var bankList = GetList();

            return bankList;
        }

        public void LoadddlBank2()
        {
            ddlBank2.DataSource = LoadBankList();
            ddlBank2.DataBind();

            ddlBank2.Items.Insert(0, new RadComboBoxItem("--เลือกทั้งหมด--", ""));
        }

        protected void LoadDdlBank()
        {
            IBLL_FIM_MAS_Banks bll = new B_FIM_MAS_Banks();

            ddlBank.DataSource = bll.GetList();
            ddlBank.DataBind();

            ddlBank.Items.Insert(0, new RadComboBoxItem("--เลือกทั้งหมด--", ""));
        }

        protected void LoadDdlBankAccount()
        {
            IBLL_FIM_MAS_BankAccounts bll = new B_FIM_MAS_BankAccounts();

            ddlBankAccount.DataSource = bll.GetList("2", null, ddlBank.SelectedValue, null);
            ddlBankAccount.DataBind();

            ddlBankAccount.Items.Insert(0, new RadComboBoxItem("--เลือกทั้งหมด--", ""));
        }

        protected void LoadBgmSource()
        {
            IBLL_FIM_Payments bll = new B_FIM_Payments();
            var source = bll.GetBgmSource();

            ddlBgmsource.DataSource = source;
            ddlBgmsource.DataBind();

            ddlVatBgmSourceSeq.DataSource = source;
            ddlVatBgmSourceSeq.DataBind();

            ddlBgmSourceSeq3.DataSource = source;
            ddlBgmSourceSeq3.DataBind();

            main.InsertEmptyDDLAll(ddlBgmsource);
            main.InsertEmptyDDLAll(ddlVatBgmSourceSeq);
            main.InsertEmptyDDLAll(ddlBgmSourceSeq3);
        }

        protected void LoadBgmsourceBorrorw()
        {
            IBLL_FIM_Payments bll = new B_FIM_Payments();

            dllBorrow.DataSource = bll.GetBgmSource();
            dllBorrow.DataBind();

            dllBorrow.Items.Insert(0, new RadComboBoxItem("--เลือกทั้งหมด--", ""));
        }

        protected void LoadBgmsourceMoneyRequestNotPay()
        {
            IBLL_FIM_Payments bll = new B_FIM_Payments();

            dllMoneyRequestNotPay.DataSource = bll.GetBgmSource();
            dllMoneyRequestNotPay.DataBind();

            dllMoneyRequestNotPay.Items.Insert(0, new RadComboBoxItem("--เลือกทั้งหมด--", ""));
        }

        protected void txtBgmYear_TextChanged(object sender, EventArgs e)
        {
            LoadDdlProject();
        }

        protected void ddlBank_SelectedIndexChanged(object sender, RadComboBoxSelectedIndexChangedEventArgs e)
        {
            LoadDdlBankAccount();
        }

        protected void ddlBank2_SelectedIndexChanged(object sender, RadComboBoxSelectedIndexChangedEventArgs e)
        {
            int test = ddlBank2.SelectedIndex;
        }

        protected void rblTaxType_SelectedIndexChanged(object sender, EventArgs e)
        {
            switch(rblTaxType.SelectedValue)
            {
                case "1":
                    tbEmployee.Visible = false;
                    tbContact.Visible = false;
                    break;
                case "2":
                    tbEmployee.Visible = true;
                    tbContact.Visible = false;
                    break;
                case "3":
                    tbEmployee.Visible = false;
                    tbContact.Visible = true;
                    break;
                case "4":
                    tbEmployee.Visible = false;
                    tbContact.Visible = false;
                    break;
            }
        }

        protected void cmdPrint1_Click(object sender, EventArgs e)
        {
            var dbManager = new DBM.DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            int engYear = int.Parse(txtYear.Text) - 543;
            int month = int.Parse(ddlMonth.SelectedValue);
            DateTime dtFrom = new DateTime(engYear, month, 1);
            DateTime dtTo = dtFrom.AddMonths(1).AddDays(-1);
            int lastDate = dtTo.Day;
            string startDate = string.Format("{0}-{1}-{2}", engYear, month, 1);
            string endDate = string.Format("{0}-{1}-{2}", engYear, month, lastDate);

            switch (ddlReportName.SelectedValue)
            {
                #region SumUpPayment
                case "SumUpPayment":
                    DateTime? dtStartDatePay = txtSumUpPaymentDateFrom.GetDateValue();
                    DateTime? dtEndDatePay = txtSumUpPaymentDateTo.GetDateValue();
                    string bgmSeqPayment = ddlBgmsource.SelectedValue.ToString();

                    if (dtStartDatePay == null || dtEndDatePay == null)
                    {
                        main.MessageAlert(Page, "กรุณากรอกวันที่ก่อนออกรายงาน");
                        return;
                    }

                    DataTable dtbPay = pay.Report_SumUpPaymentFT(dtStartDatePay, dtEndDatePay, bgmSeqPayment);

                    Dictionary<string, DataTable> dtbsPay = new Dictionary<string, DataTable>();
                    dtbsPay.Add("A3", dtbPay);

                    double dSumPay = 0;
                    foreach (DataRow dr in dtbPay.Rows)
                    {
                        dSumPay += main.ConvertNullDouble(dr["amount"]) ?? 0;
                    }

                    Dictionary<string, string> dicPay = new Dictionary<string, string>();
                    dicPay.Add("A1", "ตั้งแต่วันที่ " + txtSumUpPaymentDateFrom.GetStringValue() + " ถึงวันที่ " + txtSumUpPaymentDateTo.GetStringValue());
                    dicPay.Add("E" + (dtbPay.Rows.Count + 4), "รวม");
                    dicPay.Add("F" + (dtbPay.Rows.Count + 4), dSumPay.ToString("#,##0.00"));

                    mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/ทะเบียนคุมรายการจ่ายเงิน.xlsx"), string.Format("PaymentController-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), dtbsPay, dicPay);
                    break;
                #endregion
                #region SumUpReceive
                case "SumUpReceive":
                    DateTime? dtStartDateRec = txtSumUpPaymentDateFrom.GetDateValue();
                    DateTime? dtEndDateRec = txtSumUpPaymentDateTo.GetDateValue();
                    string bgmSeqReceive = ddlBgmsource.SelectedValue.ToString();
                    string bankSeq = ddlBank2.SelectedValue.ToString();

                    if (dtStartDateRec == null || dtEndDateRec == null)
                    {
                        main.MessageAlert(Page, "กรุณากรอกวันที่ก่อนออกรายงาน");
                        return;
                    }

                    DataTable dtbRec = null;
                    Dictionary<string, DataTable> dtbsRec = new Dictionary<string, DataTable>();
                    Dictionary<string, string> dicRec = new Dictionary<string, string>();
                    List<string> mergeFieldList = new List<string>();
                    double dSumRec = 0;
                    int nstartRow = 3;
                    int nRowRec = 3;

                    dicRec.Add("A1", "ตั้งแต่วันที่ " + txtSumUpPaymentDateFrom.GetStringValue() + " ถึงวันที่ " + txtSumUpPaymentDateTo.GetStringValue());

                    if (chkBankAccount.Checked)
                    {
                        int nOldBranchSeq = -1;
                        double dSumBranch = 0;
                        dtbsRec = null;
                        dtbRec = Report_SumUpReceiveFT2(dtStartDateRec, dtEndDateRec, bgmSeqReceive, rdlReceiveMode.SelectedValue, bankSeq);

                        for (int i = 0; i < dtbRec.Rows.Count; i++)
                        {
                            DataRow dr = dtbRec.Rows[i];
                            int nBranchSeq = main.ConvertNullInt(dr["branch_seq"]) != null ? main.ConvertNullInt(dr["branch_seq"]).Value : 0;
                            double dAmount = main.ConvertNullDouble(dr["amount"]) ?? 0;
                            double dVat = main.ConvertNullDouble(dr["vat_amount"]) ?? 0;
                            double dExVat = main.ConvertNullDouble(dr["exclude_vat"]) ?? 0;

                            if (nBranchSeq != nOldBranchSeq)
                            {
                                if (i > 0)
                                {
                                    dicRec.Add("F" + nRowRec, "รวมสาขา");
                                    dicRec.Add("G" + nRowRec, dSumBranch.ToString("#,##0.00"));
                                    nRowRec++;
                                    nRowRec++;
                                }

                                string strAccountName = dr["bank_acc_no"].ToString().Length > 0 ? dr["bank_acc_no"].ToString() + " : " + dr["branch_id"].ToString() + " - " + dr["bank_name"].ToString() + "/" + dr["branch_name"].ToString() : "ยังไม่ฝากเข้าธนาคาร/ไม่ระบุธนาคาร";

                                dicRec.Add("A" + nRowRec, strAccountName);
                                mergeFieldList.Add("A" + nRowRec + ":" + "F" + nRowRec);
                                nOldBranchSeq = nBranchSeq;
                                dSumBranch = 0;
                                nRowRec++;
                            }

                            dicRec.Add("A" + nRowRec, main.ConvertNullDateTime(dr["receive_date"]) != null ? main.ConvertNullDateTime(dr["receive_date"]).Value.ToShortDateString() : "");
                            dicRec.Add("B" + nRowRec, dr["ref_no"].ToString());
                            dicRec.Add("C" + nRowRec, dr["receipt_no"].ToString());
                            dicRec.Add("D" + nRowRec, dr["payer_name"].ToString());
                            //dicRec.Add("E" + nRowRec, dr["cheque_no"].ToString());
                            dicRec.Add("E" + nRowRec, dr["receive_type_name"].ToString());
                            dicRec.Add("F" + nRowRec, dExVat.ToString("#,##0.00"));
                            dicRec.Add("G" + nRowRec, dVat.ToString("#,##0.00"));
                            dicRec.Add("H" + nRowRec, dAmount.ToString("#,##0.00"));
                            dicRec.Add("I" + nRowRec, dr["remarks"].ToString());
                            dicRec.Add("J" + nRowRec, dr["bank_acc_no"].ToString());

                            nRowRec++;

                            dSumBranch += dAmount;
                            dSumRec += dAmount;

                            if (i == dtbRec.Rows.Count - 1)
                            {
                                dicRec.Add("G" + nRowRec, "รวมสาขา");
                                dicRec.Add("H" + nRowRec, dSumBranch.ToString("#,##0.00"));
                                nRowRec++;
                                nRowRec++;

                                dicRec.Add("G" + nRowRec, "รวม");
                                dicRec.Add("H" + nRowRec, dSumRec.ToString("#,##0.00"));
                            }
                        }
                    }
                    else
                    {
                        dtbRec = Report_SumUpReceiveFT(dtStartDateRec, dtEndDateRec, bgmSeqReceive, rdlReceiveMode.SelectedValue, bankSeq);
                        dtbsRec.Add("A3", dtbRec);

                        foreach (DataRow dr in dtbRec.Rows)
                        {
                            dSumRec += main.ConvertNullDouble(dr["amount"]) ?? 0;
                        }

                        dicRec.Add("G" + (dtbRec.Rows.Count + 4), "รวม");
                        dicRec.Add("H" + (dtbRec.Rows.Count + 4), dSumRec.ToString("#,##0.00"));
                        nRowRec = dtbRec.Rows.Count + 4;
                    }

                    int[] range = new int[4];

                    range[0] = nstartRow;
                    range[1] = 1;
                    range[2] = nRowRec;
                    range[3] = 10;

                    mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/ทะเบียนคุมรายการรับเงิน.xlsx"), string.Format("ReceiverControl-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), range, dtbsRec, dicRec, mergeFieldList);
                    break;
                #endregion
                #region SumUpBorrow
                case "SumUpBorrow":
                    DateTime? dtStartDate = txtStartDate.GetDateValue();
                    DateTime? dtEndDate = txtEndDate.GetDateValue();
                    string strBgmYear = txtBgmYear.Text;
                    string strProjectId = ddlProject.SelectedValue;
                    string strEmpId = ddlEmp.SelectedValue;
                    string bgmSeqBorrow = dllBorrow.SelectedValue.ToString();

                    DataTable dtb = report.SumUpBorrow(strBgmYear, strProjectId, strEmpId, dtStartDate, dtEndDate, bgmSeqBorrow);

                    Dictionary<string, DataTable> dtbs = new Dictionary<string, DataTable>();
                    dtbs.Add("A5", dtb);

                    mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/รายงานลูกหนี้เงินยืมรายตัว.xlsx"), string.Format("ARPersonReport-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), dtbs);
                    break;
                #endregion
                #region SumUpPaymentCheque
                case "SumUpPaymentCheque":
                    DateTime? dtStartDate2 = dpStartDate.GetDateValue();
                    DateTime? dtEndDate2 = dpEndDate.GetDateValue();
                    string strBgmYear2 = txtBgmYear2.Text;
                    string strBankSeq = ddlBank.SelectedValue;
                    string strBankAccountSeq = ddlBankAccount.SelectedValue;
                    string strChequeStatus = rblChequeStatus.SelectedValue;
                    string strBookNo = txtStartDate2.Text.Trim();
                    string strBank = "";

                    if (rblPaymentChequeType.SelectedValue == "1")
                    {
                        DataTable dtb2 = SumUpPaymentCheque(strBgmYear2, strBankSeq, strBankAccountSeq, txtStartDate2.Text.Trim(), dtStartDate2, dtEndDate2, strChequeStatus, "1");

                        DataTable tmpTab = new DataTable();

                        tmpTab.Columns.Add("ChequeNo");
                        tmpTab.Columns.Add("PayeeName");
                        tmpTab.Columns.Add("PqNo");
                        tmpTab.Columns.Add("Total");
                        tmpTab.Columns.Add("ChequeDate");
                        tmpTab.Columns.Add("PayDate");
                        tmpTab.Columns.Add("ActualPayDate");
                        tmpTab.Columns.Add("ChequeStatusName");
                        tmpTab.Columns.Add("Remarks");

                        foreach (DataRow row in dtb2.Rows)
                        {
                            tmpTab.Rows.Add(row["cheque_no"].ToString(), row["payee_name"].ToString()
                                , row["ref_no"].ToString(), string.Format("{0:0,0.##}", row["amount"])
                                , main.ConvertNullDateTime(row["cheque_date"]).HasValue ? main.ConvertNullDateTime(row["cheque_date"]).Value.ToShortDateString() : "-"
                                , main.ConvertNullDateTime(row["pay_date"]).HasValue ? main.ConvertNullDateTime(row["pay_date"]).Value.ToShortDateString() : "-"
                                , main.ConvertNullDateTime(row["actual_pay_date"]).HasValue ? main.ConvertNullDateTime(row["actual_pay_date"]).Value.ToShortDateString() : "-"
                                , row["cheque_status"].ToString(), row["remarks"].ToString()
                                );
                        }

                        Dictionary<string, string> dicCheque = new Dictionary<string, string>();
                        dicCheque.Add("A1", "ทะเบียนคุมเช็ค สมุดเช็คเลขที่ " + strBookNo + " " + strBank);

                        Dictionary<string, DataTable> dtbs2 = new Dictionary<string, DataTable>();
                        dtbs2.Add("A4", tmpTab);

                        mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/ทะเบียนคุมเช็ค.xlsx")
                            , string.Format("ChequeControlReport-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), dtbs2, dicCheque);
                    }
                    else if (rblPaymentChequeType.SelectedValue == "2")
                    {
                        DataTable dtb2 = SumUpPaymentCheque(strBgmYear2, strBankSeq, strBankAccountSeq, txtStartDate2.Text.Trim(), dtStartDate2, dtEndDate2, strChequeStatus, "2");

                        DataTable tmpTab = new DataTable();

                        tmpTab.Columns.Add("ChequeNo");
                        tmpTab.Columns.Add("PayeeName");
                        tmpTab.Columns.Add("PqNo");
                        tmpTab.Columns.Add("Total");
                        tmpTab.Columns.Add("ChequeDate");
                        tmpTab.Columns.Add("PayDate");
                        tmpTab.Columns.Add("ActualPayDate");
                        tmpTab.Columns.Add("ChequeStatusName");
                        tmpTab.Columns.Add("Remarks");

                        foreach (DataRow row in dtb2.Rows)
                        {
                            tmpTab.Rows.Add(row["cheque_no"].ToString(), row["payee_name"].ToString()
                                , row["ref_no"].ToString(), string.Format("{0:0,0.##}", row["amount"])
                                , main.ConvertNullDateTime(row["cheque_date"]).HasValue ? main.ConvertNullDateTime(row["cheque_date"]).Value.ToShortDateString() : "-"
                                , main.ConvertNullDateTime(row["pay_date"]).HasValue ? main.ConvertNullDateTime(row["pay_date"]).Value.ToShortDateString() : "-"
                                , main.ConvertNullDateTime(row["actual_pay_date"]).HasValue ? main.ConvertNullDateTime(row["actual_pay_date"]).Value.ToShortDateString() : "-"
                                , row["cheque_status"].ToString(), ""
                                );
                        }

                        Dictionary<string, string> dicCheque = new Dictionary<string, string>();
                        dicCheque.Add("A1", "รายงานการจ่ายเช็ค" + (dtStartDate2.HasValue ?  "ตั้งแต่วันที่ " + dtStartDate2.Value.ToLongDateString() : "") + (dtEndDate2.HasValue ? " ถึงวันที่ " + dtEndDate2.Value.ToLongDateString() : ""));

                        Dictionary<string, DataTable> dtbs2 = new Dictionary<string, DataTable>();
                        dtbs2.Add("A4", tmpTab);

                        mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/ทะเบียนคุมเช็ค.xlsx")
                            , string.Format("ChequeControlReport-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), dtbs2, dicCheque);
                    }
                    
                    break;
                #endregion
                #region MoneyRequestNotPay
                case "MoneyRequestNotPay":
                    string bgmsourceMoneyRequestNotPay = dllMoneyRequestNotPay.SelectedValue.ToString();
                    DataTable dtb3 = report.MoneyRequestNotPay(bgmsourceMoneyRequestNotPay);
                    string strOldBgmSourceSeq = "";
                    string strOldBgmProjSeq = "";
                    string strOldMoneyReqSeq = "";
                    int nRow = 2;

                    Dictionary<string, string> dic = new Dictionary<string, string>();

                    for (int i = 0; i < dtb3.Rows.Count; i++)
                    {
                        DataRow dr = dtb3.Rows[i];
                        string strBgmSourceSeq = dr["bgm_source_seq"].ToString();
                        string strBgmProjSeq = dr["bgm_proj_seq"].ToString();
                        string strBgmSourceName = dr["bgm_source_name"].ToString();
                        string strBgmProjName = dr["bgm_proj_name"].ToString();
                        string strMoneyReqSeq = dr["money_req_seq"].ToString();
                        string strRefNo = dr["ref_no"].ToString();
                        string strMoneyReqName = dr["money_req_name"].ToString();
                        string strEmpName = dr["emp_name"].ToString();
                        string strMoneyReqDate = dr["money_req_date"].ToString() != null ? Convert.ToDateTime(dr["money_req_date"]).ToLongDateString() : "";
                        string strBgmExpName = dr["bgm_exp_name"].ToString();
                        string strAmount = (main.ConvertNullDouble(dr["amount"]) ?? 0).ToString("#,##0.00");

                        //bgm_source_name
                        if (strOldBgmSourceSeq != strBgmSourceSeq)
                        {
                            dic.Add("A" + nRow.ToString(), "แหล่งเงิน " + strBgmSourceName);
                            strOldBgmSourceSeq = strBgmSourceSeq;
                            nRow++;
                        }

                        //bgm_project_name
                        if (strOldBgmProjSeq != strBgmProjSeq)
                        {
                            dic.Add("B" + nRow.ToString(), strBgmProjName);
                            strOldBgmProjSeq = strBgmProjSeq;
                            nRow++;
                        }

                        //money_request
                        if (strOldMoneyReqSeq != strMoneyReqSeq)
                        {
                            dic.Add("C" + nRow.ToString(), "เลขที่รายการ: " + strRefNo + " รายการ: " + strMoneyReqName);
                            nRow++;
                            dic.Add("C" + nRow.ToString(), "ผู้ทำรายการ: " + strEmpName + " วันที่ทำรายการเบิก: " + strMoneyReqDate);
                            strOldMoneyReqSeq = strMoneyReqSeq;
                            nRow++;
                            dic.Add("D" + nRow.ToString(), "รายการค่าใช้จ่าย");
                            dic.Add("E" + nRow.ToString(), "จำนวนเงิน");
                            nRow++;
                        }

                        //bgm_expense
                        dic.Add("D" + nRow.ToString(), strBgmExpName);
                        dic.Add("E" + nRow.ToString(), strAmount);
                        nRow++;

                        //sum_amount
                        if (i == dtb3.Rows.Count - 1 || strMoneyReqSeq != dtb3.Rows[i + 1]["money_req_seq"].ToString())
                        {
                            string strSumAmount = (main.ConvertNullDouble(dtb3.Rows[i]["sum_amount"]) ?? 0).ToString("#,##0.00");

                            dic.Add("D" + nRow.ToString(), "รวมทั้งหมด");
                            dic.Add("E" + nRow.ToString(), strSumAmount);
                            strOldMoneyReqSeq = strMoneyReqSeq;
                            nRow++;
                        }
                    }

                    mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/รายงานรายการค้างจ่าย.xlsx"), string.Format("PayableReport-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), null, dic);
                    break;
                #endregion
                #region Receives
                case "Receives":

                    DateTime? dtStartDateR = txtSumUpPaymentDateFrom.GetDateValue() == null ? new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1) : txtSumUpPaymentDateFrom.GetDateValue();
                    DateTime? dtEndDateR = txtSumUpPaymentDateTo.GetDateValue() == null ? new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day) : txtSumUpPaymentDateTo.GetDateValue();
                    string bgmsourceReceives = ddlBgmsource.SelectedValue.ToString() == "" ? "0" : ddlBgmsource.SelectedValue.ToString();
                    string receiveMode = rdlReceiveMode.SelectedValue;
                    //string tempIncome = (rdlReceiveMode.SelectedValue == "4" ? "True" : "False");
                    string bank2Seq = ddlBank2.SelectedValue.ToString() == "" ? "0" : ddlBank2.SelectedValue.ToString();

                    DataTable dtb4 = Receives();

                    Dictionary<string, string> dic1 = new Dictionary<string, string>();

                    int nRow1 = 4;
                    int rowCount = 1;
                    int activeCount = 0;
                    double sumExVat = 0;
                    double sumVatAmt = 0;
                    double sumAmt = 0;

                    for (int i = 0; i < dtb4.Rows.Count; i++)
                    {
                        DataRow dr = dtb4.Rows[i];

                        bool bCancel = false;

                        string strReceiveDate = main.ConvertNullDateTime(dr["receive_date"]) != null ? Convert.ToDateTime(dr["receive_date"]).ToShortDateString() : "";
                        string strRefNo = dr["ref_no"].ToString();
                        string strReceiptNo = dr["receipt_no"].ToString();
                        //string strReceivedBy = dr["emp_name"].ToString();
                        string strContactName = dr["contact_name"].ToString();
                        string strExcludeVat = (main.ConvertNullDouble(dr["exclude_vat"]) ?? 0).ToString("#,##0.00");
                        string strVatAmount = (main.ConvertNullDouble(dr["vat_amount"]) ?? 0).ToString("#,##0.00");
                        string strAmount = (main.ConvertNullDouble(dr["amount"]) ?? 0).ToString("#,##0.00");
                        string strRecordStatus = dr["record_status"].ToString();
                        string strReceiveTypeName = dr["receive_type_name"].ToString();
                        string strDiscount = (main.ConvertNullDouble(dr["discount"]) ?? 0).ToString("#,##0.00");
                        string strStartDateR = Convert.ToDateTime(dtStartDateR).ToShortDateString();
                        string strEnddateR = Convert.ToDateTime(dtEndDateR).ToShortDateString();
                        string strBgmSourceSeq = dr["bgm_source_seq"].ToString();
                        string strTempIncome = dr["temp_income"].ToString();
                        string strBank2Seq = dr["bank_acc_seq"].ToString();
                        string createdWhen = main.ConvertNullDateTime(dr["receive_date"]) != null ? Convert.ToDateTime(dr["receive_date"]).ToShortDateString() : "";
                        string strBankAccNo = dr["bank_acc_no"].ToString();

                        strExcludeVat = (double.Parse(strExcludeVat) - double.Parse(strDiscount)).ToString("#,##0.00");

                        if (strRecordStatus == "C" || strRecordStatus == "D")
                        {
                            bCancel = true;
                        }

                        if ((receiveMode == dr["receive_mode"].ToString())
                            && (chkBgmSourceAll(bgmsourceReceives, strBgmSourceSeq))
                            && ((main.ConvertNullDateTime(dr["receive_date"]) >= txtSumUpPaymentDateFrom.GetDateValue())
                            && (main.ConvertNullDateTime(dr["receive_date"]) <= txtSumUpPaymentDateTo.GetDateValue()))
                            && (strTempIncome == "False" || string.IsNullOrEmpty(strTempIncome))
                            && (chkBank2All(bank2Seq, strBank2Seq))
                            )
                        {
                            dic1.Add("A" + nRow1.ToString(), rowCount.ToString());
                            dic1.Add("B" + nRow1.ToString(), strReceiveDate);
                            dic1.Add("C" + nRow1.ToString(), strReceiptNo);
                            dic1.Add("D" + nRow1.ToString(), strRefNo);
                            dic1.Add("E" + nRow1.ToString(), bCancel ? "" : strContactName);
                            dic1.Add("F" + nRow1.ToString(), bCancel ? "0.00" : strExcludeVat);
                            dic1.Add("G" + nRow1.ToString(), bCancel ? "0.00" : strVatAmount);
                            dic1.Add("H" + nRow1.ToString(), bCancel ? "0.00" : strAmount);
                            dic1.Add("I" + nRow1.ToString(), bCancel ? "" : strReceiveTypeName);

                            //if (strRecordStatus == "C")
                            //{
                            //    dic1.Add("J" + nRow1.ToString(), "ยกเลิกใบเสร็จ");
                            //}
                            //else if (strRecordStatus == "D")
                            //{
                            //    dic1.Add("J" + nRow1.ToString(), "ยกเลิกใบเสร็จ");
                            //}
                            //else
                            //{
                            //    dic1.Add("J" + nRow1.ToString(), "ใช้งานอยู่");
                            //    sumExVat += double.Parse(strExcludeVat);
                            //    sumVatAmt += double.Parse(strVatAmount);
                            //    sumAmt += double.Parse(strAmount);
                            //}

                            if (bCancel)
                            {
                                dic1.Add("J" + nRow1.ToString(), "ยกเลิกใบเสร็จ");
                            }
                            else
                            {
                                dic1.Add("J" + nRow1.ToString(), "ถูกใช้งานแล้ว");
                                sumExVat += double.Parse(strExcludeVat);
                                sumVatAmt += double.Parse(strVatAmount);
                                sumAmt += double.Parse(strAmount);
                                activeCount += 1;
                            }

                            dic1.Add("K" + nRow1.ToString(), bCancel ? "" : strBankAccNo);

                            nRow1++;
                            rowCount++;
                        }
                    }

                    dic1.Add("E" + nRow1.ToString(), string.Format("รายการที่ใช้งาน {0} รายการ", activeCount));
                    dic1.Add("F" + nRow1.ToString(), sumExVat.ToString("#,##0.00"));
                    dic1.Add("G" + nRow1.ToString(), sumVatAmt.ToString("#,##0.00"));
                    dic1.Add("H" + nRow1.ToString(), sumAmt.ToString("#,##0.00"));

                    mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/ทะเบียนคุมใบเสร็จรับเงิน.xlsx"), string.Format("ARPersonReport-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), null, dic1);

                    break;
                #endregion
                #region VatBuy
                case "VatBuy":
                    if (string.IsNullOrEmpty(txtYear.Text)) return;

                    DataTable dt1 = VatBuy();

                    Dictionary<string, DataTable> dtbPays = new Dictionary<string, DataTable>();

                    dtbPays.Add("Buy!A9", dt1);

                    int sumRow = 9 + dt1.Rows.Count;
                    int[] position = new int[4];
                    double col1 = 0;
                    double col2 = 0;
                    double col3 = 0;

                    position[0] = 9;
                    position[1] = 1;
                    position[2] = sumRow;
                    position[3] = 9;

                    foreach (DataRow r in dt1.Rows)
                    {
                        col1 += double.Parse(r["amount"].ToString());
                        col2 += double.Parse(r["amountExcludeVat"].ToString());
                        col3 += double.Parse(r["Vat"].ToString());
                    }

                    string strDate = "เดือนที่คิดภาษี" + " " + ddlMonth.Text + " " + "ปี " + txtYear.Text;
                    Dictionary<string, string> selDate = new Dictionary<string, string>();

                    selDate.Add("Buy!A2", strDate);
                    selDate.Add(string.Format("Buy!F{0}", sumRow), "ยอดรวม ");
                    selDate.Add(string.Format("Buy!I{0}", sumRow), col1.ToString("#,##0.00"));
                    selDate.Add(string.Format("Buy!G{0}", sumRow), col2.ToString("#,##0.00"));
                    selDate.Add(string.Format("Buy!H{0}", sumRow), col3.ToString("#,##0.00"));

                    mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/VatBuy.xlsx"), string.Format("VatBuy{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), position, dtbPays, selDate);
                    break;
                #endregion
                #region VatSale
                case "VatSale":
                    if (string.IsNullOrEmpty(txtYear.Text)) return;

                    dbManager.Open();
                    dbManager.CreateParameters(3);
                    dbManager.AddParameters(0, "@Strmonth", ddlMonth.SelectedValue);
                    dbManager.AddParameters(1, "@stryear", txtYear.Text);
                    dbManager.AddParameters(2, "@bgm_source_seq", ddlVatBgmSourceSeq.SelectedValue);

                    string strSQL2 = @"
select ROW_NUMBER()OVER(ORDER BY t.date, t.receiptNo) AS Row , *
from (
select 
a.receive_date as date,a.receipt_no as receiptNo 
,(case when a.contact_seq is not null then b.NameTh else a.temp_contact end) as ConName 
,(case when a.contact_seq is not null then b.TaxCode else a.contact_tax_code end) as CodeCon 
, a.contact_branch as BranchNo 
,(case a.vat_amount when 0 then 0 else a.amount-a.vat_amount end) as amount 
, a.vat_amount as vatamount,( case a.vat_amount when 0 then 0 else a.amount END ) as IncludeVat 
,(case a.vat_amount when 0 then a.amount else 0 END) as a 
from fim_receives a 
inner join fim_vat_dtls vd on a.receive_seq = vd.receive_seq 
inner join fim_vat v on vd.vat_seq = v.vat_seq and v.record_status <> 'D' 
left join IcmMasContact b on a.contact_seq=b.ConId 
left join (select bank.bank_seq,acc.bank_acc_seq,bank.bank_name
	from fim_mas_bank_accounts acc
	inner join fim_mas_bank_branchs brch on acc.branch_seq = brch.branch_seq
	inner join fim_mas_banks bank on brch.bank_seq = bank.bank_seq) as banks on a.bank_acc_seq = banks.bank_acc_seq 

left join fim_mas_receive_types rt on a.receive_type_seq = rt.receive_type_seq 
where v.year_no = @stryear 
and v.month_no = @Strmonth"
+ (string.IsNullOrEmpty(ddlVatBgmSourceSeq.SelectedValue) ? "" : " and a.bgm_source_seq = @bgm_source_seq")
+ @" union all

select p.pay_date as date, p.ref_no as receiptNo
, case when p.contact_seq is not null then c.NameTh else concat(np.prefix_name, emp.first_name, ' ', emp.last_name) end as ConName
, case when p.contact_seq is not null then c.TaxCode else emp.citizen end as CodeCon
, null as BranchNo 
, case when vd.vat_buy_amount > 0 then - coalesce(p.amount, 0) + coalesce(p.vat, 0) else coalesce(p.amount, 0) - coalesce(p.vat, 0) end as amount
, case when vd.vat_buy_amount > 0 then - coalesce(p.vat, 0) else coalesce(p.vat, 0) end as vatamount
, case when vd.vat_buy_amount > 0 then - coalesce(p.amount, 0) else coalesce(p.amount, 0) end as IncludeVat 
, 0 as a 
from fim_payments p
left join IcmMasContact c on p.contact_seq = c.ConId
left join hrm_ps_employees emp on p.emp_seq = emp.emp_seq
left join mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq
--left join fim_receives r on p.pay_seq = r.pay_seq
inner join fim_vat_dtls vd on p.pay_seq = vd.pay_seq 
inner join fim_vat v on vd.vat_seq = v.vat_seq and v.record_status <> 'D' 

where v.year_no = @stryear 
and v.month_no = @Strmonth
and p.event_seq = 27"
+ (string.IsNullOrEmpty(ddlVatBgmSourceSeq.SelectedValue) ? "" : " and p.bgm_source_seq = @bgm_source_seq")
+ @")t
order by t.date, t.receiptNo
";

                    DataTable dt2 = dbManager.ExecuteDataSet(CommandType.Text, strSQL2).Tables[0];
                    Dictionary<string, DataTable> dtbRecs = new Dictionary<string, DataTable>();
                    dbManager.Close();
                    dtbRecs.Add("Sale!A9", dt2);

                    int sumRow2 = 9 + dt2.Rows.Count;
                    int[] position2 = new int[4];
                    decimal c1 = 0;
                    decimal c2 = 0;
                    decimal c3 = 0;
                    decimal c4 = 0;

                    position2[0] = 9;
                    position2[1] = 1;
                    position2[2] = sumRow2;
                    position2[3] = 10;


                    foreach (DataRow r in dt2.Rows)
                    {
                        c1 += decimal.Parse(r["amount"].ToString());
                        c2 += decimal.Parse(r["vatamount"].ToString());
                        c3 += decimal.Parse(r["includevat"].ToString());
                        c4 += decimal.Parse(r["a"].ToString());
                    }

                    c2 = c3 * 7 / 107;
                    c1 = c3 - c2;

                    string strDate2 = "เดือนที่คิดภาษี" + " " + ddlMonth.Text + " " + "ปี " + txtYear.Text;
                    Dictionary<string, string> selDate2 = new Dictionary<string, string>();

                    selDate2.Add("Sale!D3", strDate2);
                    selDate2.Add(string.Format("Sale!F{0}", sumRow2), "ยอดรวม ");
                    selDate2.Add(string.Format("Sale!G{0}", sumRow2), c1.ToString("#,##0.00"));
                    selDate2.Add(string.Format("Sale!H{0}", sumRow2), c2.ToString("#,##0.00"));
                    selDate2.Add(string.Format("Sale!I{0}", sumRow2), c3.ToString("#,##0.00"));
                    selDate2.Add(string.Format("Sale!J{0}", sumRow2), c4.ToString("#,##0.00"));

                    mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/VatSale.xlsx"), string.Format("VatSale{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), position2, dtbRecs, selDate2);
                    break;
                #endregion
                #region Collateral
                case "Collateral":
                    DateTime? dtStartDateC = dtpCollateralStartDate.GetDateValue() == null ? new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1) : dtpCollateralStartDate.GetDateValue();
                    DateTime? dtEndDateC = dtpCollateralEndDate.GetDateValue() == null ? new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day) : dtpCollateralEndDate.GetDateValue();

                    DataTable dtb5 = Collateral(dtStartDateC.Value, dtEndDateC.Value);
                    Dictionary<string, DataTable> dicDtb = new Dictionary<string, DataTable>();
                    Dictionary<string, string> dic2 = new Dictionary<string, string>();
                    double dSumRemain = 0;

                    foreach (DataRow dr in dtb5.Rows)
                    {
                        dSumRemain += main.ConvertNullDouble(dr["total"]) ?? 0;
                    }

                    dicDtb.Add("Sheet1!A4", dtb5);
                    dic2.Add("Sheet1!A2", "ตั้งแต่วันที่ " + (dtStartDateC.HasValue ? dtStartDateC.Value.ToLongDateString() : "-") + " ถึงวันที่ " + (dtEndDateC.HasValue ? dtEndDateC.Value.ToLongDateString() : "-"));
                    dic2.Add("Sheet1!j" + (4 + dtb5.Rows.Count), dSumRemain.ToString("#,##0.00"));

                    mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/Collateral.xlsx"), string.Format("Collateral-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), dicDtb, dic2);
                    break;
                #endregion
                #region Cover / RemainMoneyRequest / ReceiveSummary
                case "Cover":
                    printCover();
                    break;
                case "RemainMoneyRequest":
                    RemainMoneyRequest();
                    break;
                case "ReceiveSummary":
                    DateTime? dtReceiveSummaryDateStart = txtReceiveSummaryDateStart.GetDateValue();
                    DateTime? dtReceiveSummaryDateEnd = txtReceiveSummaryDateEnd.GetDateValue();

                    if (!dtReceiveSummaryDateStart.HasValue || !dtReceiveSummaryDateEnd.HasValue)
                    {
                        main.MessageAlert(Page, "กรุณากรอกเงื่อนไขการออกรายงานให้ครบถ้วน");
                        return;
                    }

                    GetReceiveSummary(dtReceiveSummaryDateStart.Value, dtReceiveSummaryDateEnd.Value);
                    break;
                #endregion
                #region Tax
                case "Tax":
                    DataTable dtbTax;

                    switch (rblTaxType.SelectedValue)
                    {
                        case "1":
                            //Response.Redirect("RdsPopup.aspx");
                            ReportDocument cryRpt = new ReportDocument();
                            dtbTax = GetTax1(txtYear1.Text);
                            //Tax1 rptTax1 = new Tax1();
                            cryRpt.Load(Server.MapPath("~//Reports//Tax1.rpt"));
                            cryRpt.SetDataSource(dtbTax);
                            cryRpt.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, true, "Tax.pdf");
                            cryRpt.Dispose();
                            cryRpt.Close();
                            break;
                        case "2":
                            break;
                        case "3":
                            break;
                        case "4":
                            break;
                    }



                    break;
                #endregion
                #region comment
                //                case "RQPQDN":
//                    string strReportHeader = "";
//                    string strRQPQDNBgmSourceSeq = ddlBgmRQPQDNSourceSeq.SelectedValue;
//                    DateTime? dtRQPQDNStartDate = txtRQPQDNDateStart.GetDateValue();
//                    DateTime? dtRQPQDNEndDate = txtRQPQDNDateEnd.GetDateValue();

//                    try
//                    {
//                        dbManager.Open();
//                        dbManager.CreateParameters(6);
//                        dbManager.AddParameters(0, "@approve_status", SystemInfo._ApproveRecordStatus);
//                        dbManager.AddParameters(1, "@bgm_source_seq", strRQPQDNBgmSourceSeq);
//                        dbManager.AddParameters(2, "@ref_no_start", ref_no_start);
//                        dbManager.AddParameters(3, "@ref_no_end", ref_no_end);
//                        dbManager.AddParameters(4, "@dtStart", dtStart);
//                        dbManager.AddParameters(5, "@dtEnd", dtEnd);

//                        strSQLRQPQDN = @"
//select mr.ref_no as rq
//, mr.money_req_date as rq_date
//, mr.money_req_name as rq_name
//,	mrd.amount
//, p.ref_no as pq
//, p.pay_date as pq_date
//, dn.Code as dn
//, dn.DeliveryDate as dn_date
//
//from fim_money_requests mr
//left join (select money_req_seq
//	, sum(coalesce(amount, 0)) as amount
//	from fim_money_request_dtls
//	where record_status <> 'D'
//	group by money_req_seq
//	) mrd on mr.money_req_seq = mrd.money_req_seq
//left join fim_payments p on mr.money_req_seq = p.money_req_seq and p.record_status = 'A'
//left join IcmDeliveryNote dn on mr.icm_dn_seq = dn.DnId and dn.RecordStatus = 'A'
//
//where mr.record_status = 'A'"
//                            + (!string.IsNullOrEmpty(bgm_source_seq) ? " and mr.bgm_source_seq = @bgm_source_seq" : "")
//                            + (dtStart.HasValue ? " and mr.money_req_date >= @dtStart" : "")
//                            + (dtEnd.HasValue ? " and mr.money_req_date <= @dtEnd" : "")
//                            + (!string.IsNullOrEmpty(ref_no_start) ? " and mr.ref_no >= @ref_no_start" : "")
//                            + (!string.IsNullOrEmpty(ref_no_end) ? " and mr.ref_no <= @ref_no_end" : "")
//                            + " ORDER BY mr.ref_no";

//                        dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
//                        return dtb;
//                    }
//                    catch (Exception ex)
//                    {
//                        throw (ex);
//                    }
//                    finally
//                    {
//                        dbManager.Dispose();
//                    }


//                    DataTable dtbRQPQDN = rpt.ReportRQ_PQ_DN(strBgmSourceSeq, txtRefNoStart.Text, txtRefNoEnd.Text, dtStartDate, dtEndDate);
//                    Dictionary<string, DataTable> dic = new Dictionary<string, DataTable>();
//                    dic.Add("A5", dtb);

//                    Dictionary<string, string> dic2 = new Dictionary<string, string>();

//                    if (rblType.SelectedValue == "1")
//                    {
//                        strReportHeader = string.Format("ตั้งแต่ RQ วันที่ {0} ถึงวันที่ {1}", dtStartDate.Value.ToLongDateString(), dtEndDate.Value.ToLongDateString());
//                    }
//                    else
//                    {
//                        strReportHeader = string.Format("ตั้งแต่ RQ เลขที่ {0} ถึงเลขที่ {1}", txtRefNoStart.Text, txtRefNoEnd.Text);
//                    }
//                    dic2.Add("A3", strReportHeader);

//                    mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/RQ-PQ-DN.xlsx"), string.Format("RQ-PQ-DN-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), dic, dic2);
                //                    break;
                #endregion
                default:
                    break;
            }
        }

        protected void cmdPrint3_Click(object sender, EventArgs e)
        {
            switch (ddlReportName.SelectedValue)
            {
                case "SumUpPaymentCheque":
                    //DateTime? dtStartDate2 = txtStartDate2.GetDateValue();
                    //DateTime? dtEndDate2 = txtEndDate2.GetDateValue();
                    string strBgmYear2 = txtBgmYear2.Text;
                    string strBankSeq = ddlBank.SelectedValue;
                    string strBankAccountSeq = ddlBankAccount.SelectedValue;
                    string strChequeStatus = rblChequeStatus.SelectedValue;

                    DataTable dtb2 = report.SumUpPaymentCheque(strBgmYear2, strBankSeq, strBankAccountSeq, txtStartDate2.Text.Trim(), null, strChequeStatus);

                    Dictionary<string, DataTable> dtbs2 = new Dictionary<string, DataTable>();
                    dtbs2.Add("A4", dtb2);

                    mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/ทะเบียนคุมเช็ค_2.xlsx"), string.Format("ChequeControlReportByPQ-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), dtbs2);
                    break;
            }
        }

        public DataTable Report_SumUpReceiveFT(Nullable<DateTime> dateFrom, Nullable<DateTime> dateTo, string bgm_source_seq, string receive_mode)
        {
            string strSQL;
            DataTable dtb = null;

            var dbManager = new DBM.DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@from_date", dateFrom);
                dbManager.AddParameters(1, "@to_date", dateTo);
                dbManager.AddParameters(2, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(3, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(4, "@receive_mode", receive_mode);

                strSQL = "SELECT r.created_when as receive_date, r.ref_no, r.receipt_no"

                    + ", case when coalesce(r.temp_contact, '') <> '' then r.temp_contact"
                        + " else case when r.contact_seq is not null then c.NameTh"
                            + " else concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                            + " end"
                        + " end as payer_name"
                    //+ ", r.cheque_no" //ไม่ต้องการให้แสดงข้อมูลเช็คในรายงานทะเบียนคุมการรับเงิน
                    + ", CASE WHEN r.receive_mode = '1' THEN CONCAT('รับเงินคืนจากรายการรอเบิกที่ ', mr.ref_no)"
                        + " ELSE CASE WHEN r.receive_mode = '3' THEN CONCAT('รับเงินจากรายการขอเบิกที่ ', pt.ref_no)"
                            + " ELSE rt.receive_type_name"
                            + " END"
                        + " END AS receive_type_name"
                    + ", r.exclude_vat, r.vat_amount, r.amount, r.remarks"

                    //+ ", acc.bank_acc_seq, acc.bank_acc_no, acc.bank_acc_name"
                    //+ ", bb.branch_seq, bb.branch_id, bb.branch_name"
                    //+ ", b.bank_seq, b.bank_id, b.bank_name"

                    + " FROM fim_receives r"
                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c. ConId"
                    + " LEFT JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp on emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " LEFT JOIN fim_transfers tf on r.deposit_seq = tf.transfer_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc on tf.to_bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb on acc.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b on bb.bank_seq = b.bank_seq"
                    + " WHERE r.record_status not in ('D', 'C')"
                    + " and r.receive_mode = @receive_mode"
                    + (bgm_source_seq != "" ? " and r.bgm_source_seq like @bgm_source_seq" : "")
                    + (dateFrom != null ? " and r.receive_date >= @from_date" : "")
                    + (dateTo != null ? " and r.receive_date <= @to_date" : "")
                    + " order by r.receive_date, r.receipt_no";


                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable Report_SumUpReceiveFT(Nullable<DateTime> dateFrom, Nullable<DateTime> dateTo, string bgm_source_seq, string receive_mode, string bank_seq)
        {
            string strSQL;
            DataTable dtb = null;

            var dbManager = new DBM.DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@from_date", dateFrom);
                dbManager.AddParameters(1, "@to_date", dateTo);
                dbManager.AddParameters(2, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(3, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(4, "@receive_mode", receive_mode);
                dbManager.AddParameters(5, "@bank_seq", bank_seq);

                strSQL = "SELECT r.receive_date, r.ref_no, r.receipt_no"

                    + ", case when coalesce(r.temp_contact, '') <> '' then r.temp_contact"
                        + " else case when r.contact_seq is not null then c.NameTh"
                            + " else concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                            + " end"
                        + " end as payer_name"
                    //+ ", r.cheque_no" //ไม่ต้องการให้แสดงข้อมูลเช็คในรายงานทะเบียนคุมการรับเงิน
                    + ", CASE WHEN r.receive_mode = '1' THEN CONCAT('รับเงินคืนจากรายการรอเบิกที่ ', mr.ref_no)"
                        + " ELSE CASE WHEN r.receive_mode = '3' THEN CONCAT('รับเงินจากรายการขอเบิกที่ ', pt.ref_no)"
                            + " ELSE rt.receive_type_name"
                            + " END"
                        + " END AS receive_type_name"
                    + ", r.exclude_vat, r.vat_amount, r.amount, r.remarks, banks.bank_acc_no"

                    //+ ", acc.bank_acc_seq, acc.bank_acc_no, acc.bank_acc_name"
                    //+ ", bb.branch_seq, bb.branch_id, bb.branch_name"
                    //+ ", b.bank_seq, b.bank_id, b.bank_name"

                    + " FROM fim_receives r"
                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c. ConId"
                    + " LEFT JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp on emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " LEFT JOIN fim_transfers tf on r.deposit_seq = tf.transfer_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc on tf.to_bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb on acc.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b on bb.bank_seq = b.bank_seq"
                    //+ " RIGHT OUTER JOIN fim_receives_log ON r.receive_seq = fim_receives_log.receive_seq"
                    // Join เพื่อเพิ่มข้อมูลธนาคารสำหรับตัวกรองรายงานแยกตามธนาคาร
                    + " left join (select bank.bank_seq,acc.bank_acc_seq,bank.bank_name, acc.bank_acc_no from fim_mas_bank_accounts acc inner join"
                        + " fim_mas_bank_branchs brch on acc.branch_seq = brch.branch_seq inner join"
                        + " fim_mas_banks bank on brch.bank_seq = bank.bank_seq) as banks on r.bank_acc_seq = banks.bank_acc_seq"
                    // -----------------------------
                    + " WHERE r.record_status not in ('C', 'D')"
                    + " and r.receive_mode = @receive_mode"
                    + (bgm_source_seq != "" ? " and r.bgm_source_seq like @bgm_source_seq" : "")
                    + (dateFrom != null ? " and r.receive_date >= @from_date" : "")
                    + (dateTo != null ? " and r.receive_date <= @to_date" : "")
                    + (bank_seq != "" ? " and banks.bank_acc_seq = @bank_seq" : "")
                    + " and rt.temp_income = 0"
                    + " order by r.receive_date, r.receipt_no";


                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable Report_SumUpReceiveFT2(Nullable<DateTime> dateFrom, Nullable<DateTime> dateTo, string bgm_source_seq, string receive_mode)
        {
            string strSQL;
            DataTable dtb = null;

            var dbManager = new DBM.DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@from_date", dateFrom);
                dbManager.AddParameters(1, "@to_date", dateTo);
                dbManager.AddParameters(2, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(3, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(4, "@receive_mode", receive_mode);


                strSQL = "SELECT r.created_when as receive_date, r.ref_no, r.receipt_no"

                    + ", case when coalesce(r.temp_contact, '') <> '' then r.temp_contact"
                        + " else case when r.contact_seq is not null then c.NameTh"
                            + " else concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                            + " end"
                        + " end as payer_name"
                    + ", r.cheque_no"
                    + ", CASE WHEN r.receive_mode = '1' THEN CONCAT('รับเงินคืนจากรายการรอเบิกที่ ', mr.ref_no)"
                        + " ELSE CASE WHEN r.receive_mode = '3' THEN CONCAT('รับเงินจากรายการขอเบิกที่ ', pt.ref_no)"
                            + " ELSE rt.receive_type_name"
                            + " END"
                        + " END AS receive_type_name"
                    + ", r.exclude_vat, r.vat_amount, r.amount, r.remarks"

                    + ", acc.bank_acc_seq, acc.bank_acc_no, acc.bank_acc_name"
                    + ", bb.branch_seq, bb.branch_id, bb.branch_name"
                    + ", b.bank_seq, b.bank_id, b.bank_name"

                    + " FROM fim_receives r"
                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c. ConId"
                    + " LEFT JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp on emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " LEFT JOIN fim_transfers tf on r.deposit_seq = tf.transfer_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc on tf.to_bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb on acc.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b on bb.bank_seq = b.bank_seq"
                    + " WHERE r.record_status not in ('C', 'D')"
                    + " and r.receive_mode = @receive_mode"
                    + (bgm_source_seq != "" ? " and r.bgm_source_seq like @bgm_source_seq" : "")
                    + (dateFrom != null ? " and r.receive_date >= @from_date" : "")
                    + (dateTo != null ? " and r.receive_date <= @to_date" : "")
                    + " order by bb.branch_seq, r.receive_date, r.receipt_no";


                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable Report_SumUpReceiveFT2(Nullable<DateTime> dateFrom, Nullable<DateTime> dateTo, string bgm_source_seq, string receive_mode, string bank_seq)
        {
            string strSQL;
            DataTable dtb = null;

            var dbManager = new DBM.DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@from_date", dateFrom);
                dbManager.AddParameters(1, "@to_date", dateTo);
                dbManager.AddParameters(2, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(3, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(4, "@receive_mode", receive_mode);
                dbManager.AddParameters(5, "@bank_seq", bank_seq);

                strSQL = "SELECT fim_receives.receive_date, r.ref_no, r.receipt_no"

                    + ", case when coalesce(r.temp_contact, '') <> '' then r.temp_contact"
                        + " else case when r.contact_seq is not null then c.NameTh"
                            + " else concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                            + " end"
                        + " end as payer_name"
                    + ", r.cheque_no"
                    + ", CASE WHEN r.receive_mode = '1' THEN CONCAT('รับเงินคืนจากรายการรอเบิกที่ ', mr.ref_no)"
                        + " ELSE CASE WHEN r.receive_mode = '3' THEN CONCAT('รับเงินจากรายการขอเบิกที่ ', pt.ref_no)"
                            + " ELSE rt.receive_type_name"
                            + " END"
                        + " END AS receive_type_name"
                    + ", r.exclude_vat, r.vat_amount, r.amount, r.remarks"

                    + ", acc.bank_acc_seq, acc.bank_acc_no, acc.bank_acc_name"
                    + ", bb.branch_seq, bb.branch_id, bb.branch_name"
                    + ", b.bank_seq, b.bank_id, b.bank_name, banks.bank_acc_no"

                    + " FROM fim_receives r"
                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c. ConId"
                    + " LEFT JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp on emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " LEFT JOIN fim_transfers tf on r.deposit_seq = tf.transfer_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc on tf.to_bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb on acc.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b on bb.bank_seq = b.bank_seq"
                    //+ " RIGHT OUTER JOIN fim_receives_log ON r.receive_seq = fim_receives_log.receive_seq"
                    //// Join เพื่อเพิ่มข้อมูลธนาคารสำหรับตัวกรองรายงานแยกตามธนาคาร
                    + " left join (select bank.bank_seq,acc.bank_acc_seq,bank.bank_name, acc.bank_acc_no from fim_mas_bank_accounts acc inner join"
                        + " fim_mas_bank_branchs brch on acc.branch_seq = brch.branch_seq inner join"
                        + " fim_mas_banks bank on brch.bank_seq = bank.bank_seq) as banks on r.bank_acc_seq = banks.bank_acc_seq"
                    // -----------------------------
                    + " WHERE r.record_status not in ('D', 'C')"
                    + " and r.receive_mode = @receive_mode"
                    + (bgm_source_seq != "" ? " and r.bgm_source_seq like @bgm_source_seq" : "")
                    + (dateFrom != null ? " and r.receive_date >= @from_date" : "")
                    + (dateTo != null ? " and r.receive_date <= @to_date" : "")
                    + (bank_seq != "" ? " and banks.bank_seq = @bank_seq" : "")
                    + " and rt.temp_income = 0"
                    + " order by bb.branch_seq, r.receive_date, r.receipt_no";


                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable SumUpPaymentCheque(string BgYear, string BankSeq, string BankAccSeq
                                            , string startChequeBookNo, DateTime? StartDate
                                            , DateTime? EndDate, string strChequeStatus, string ReportType)
        {
            string strSQL;
            DataTable dtb = null;

            var dbManager = new DBM.DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_year", BgYear);
                dbManager.AddParameters(2, "@bank_seq", BankSeq);
                dbManager.AddParameters(3, "@bank_acc_seq", BankAccSeq);
                dbManager.AddParameters(4, "@start_cheque_book_no", startChequeBookNo);
                dbManager.AddParameters(5, "@start_date", StartDate);
                dbManager.AddParameters(6, "@end_date", EndDate);
                dbManager.AddParameters(7, "@cheque_status", strChequeStatus);

                strSQL = @"
select cq.cheque_no, cq.cheque_date, cq.pay_date, cq.actual_pay_date, p.ref_no, pc.amount
, case when p.contact_seq is not null then c.NameTh else concat(np.prefix_name, emp.first_name, '  ', emp.last_name) end as payee_name
, (case cq.cheque_status when '1' then 'พร้อมใช้' when '2' then 'จ่ายแล้ว' when '3' then 'ขึ้นเงิน' else 'ยกเลิก' end) as cheque_status
, cq.remarks
from fim_cheques cq
inner join fim_cheque_books cb on cq.cheque_book_seq = cb.cheque_book_seq
left join fim_payment_cheques pc on cq.cheque_seq = pc.cheque_seq
	and pc.record_status <> 'D'
left join fim_payments p on pc.pay_seq = p.pay_seq
	and p.record_status <> 'D'
left join IcmMasContact c on p.contact_seq = c.ConId
left join hrm_ps_employees emp on p.emp_seq = emp.emp_seq
left join mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq
where cq.record_status <> 'D'"
                    //+ (!string.IsNullOrEmpty(BankSeq) ? " AND fim_mas_banks.bank_seq = @bank_seq" : "")
                    //+ (!string.IsNullOrEmpty(BankAccSeq) ? " AND fim_cheques.bank_acc_seq = @bank_acc_seq" : "")
                    + (!string.IsNullOrEmpty(startChequeBookNo) ? " AND cb.cheque_book_no = @start_cheque_book_no" : "")
                    + (StartDate.HasValue && EndDate.HasValue ? " AND cq.cheque_date between @start_date and @end_date" : "")
                    + (!string.IsNullOrEmpty(strChequeStatus) ? 
                        (strChequeStatus == "15" ? " AND cq.cheque_status = '1' and p.ref_no is not null" : 
                            (strChequeStatus == "1" ? " AND cq.cheque_status = '1' and p.ref_no is null" : " AND cq.cheque_status = @cheque_status")) : "")
                    + (ReportType == "2" ? " and p.pay_seq is not null ORDER BY p.ref_no, cq.cheque_no" : " ORDER BY cq.cheque_no,p.ref_no");

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dtb;
        }

        public DataTable Receives()
        {
            string strSQL;
            DataTable dtb = null;

            var dbManager = new DBM.DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();

                strSQL = "SELECT r.receipt_no, r.ref_no, r.receive_date"
                    + ", null as receivelog_date, r.received_by, r.receive_mode, r.bgm_source_seq"
                    + ", r.exclude_vat, r.vat_amount, r.amount, r.discount, r.record_status"
                    + ", fim_mas_receive_types.receive_type_name,fim_mas_receive_types.temp_income"
                    //+ ", hrm_ps_employees_1.first_name + ' ' + hrm_ps_employees_1.last_name AS emp_name"
                    + ", banks.bank_seq, banks.bank_name, banks.bank_acc_no, banks.bank_acc_seq"
                    + @", iif(r.temp_contact is not null, r.temp_contact, iif(r.contact_seq is not null"
                    + ", c.nameth, hrm_ps_employees.first_name + ' ' + hrm_ps_employees.last_name)) as contact_name"
                    + " FROM fim_receives r"
                    + " left join IcmMasContact c on r.contact_seq = c.ConId"
                    + " LEFT JOIN hrm_ps_employees ON r.emp_seq = hrm_ps_employees.emp_seq"
                    + " LEFT JOIN fim_mas_receive_types ON r.receive_type_seq = fim_mas_receive_types.receive_type_seq"
                    //+ " LEFT JOIN hrm_ps_employees AS hrm_ps_employees_1 ON r.received_by = hrm_ps_employees_1.emp_seq"
                    //+ " RIGHT OUTER JOIN fim_receives_log ON fim_receives.receive_seq = fim_receives_log.receive_seq"
                    // Join เพื่อเพิ่มข้อมูลธนาคารสำหรับตัวกรองรายงานแยกตามธนาคาร
                    + " left join (select bank.bank_seq,acc.bank_acc_seq,bank.bank_name, acc.bank_acc_no"
                        + " from fim_mas_bank_accounts acc"
                        + " inner join fim_mas_bank_branchs brch on acc.branch_seq = brch.branch_seq"
                        + " inner join fim_mas_banks bank on brch.bank_seq = bank.bank_seq"
                        + ") as banks on r.bank_acc_seq = banks.bank_acc_seq"

                    + " where r.record_status not in ('D')"
                    // -----------------------------
                    + " ORDER BY r.receipt_no";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dtb;
        }

        public DataTable Collateral(DateTime sdate, DateTime edate)
        {
            string strSQL;
            DataTable dtb = null;
            //string sd = string.Format("'{0}-{1}-{2}'", sdate.Year, sdate.Month, sdate.Day);
            //string ed = string.Format("'{0}-{1}-{2}'", edate.Year, edate.Month, edate.Day);

            var dbManager = new DBM.DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@start", sdate);
                dbManager.AddParameters(1, "@end", edate);

                strSQL = @"
select po.Code, c.NameTh, bid.amount as po_amount, r.receive_date as BidderColDate
, r.ref_no as r_ref_no
, r.amount as r_amount, col.ReturnDueDate

, p.pay_date
, p.ref_no as p_ref_no

, coalesce(r.amount, 0) - coalesce(p.amount, 0) as total
--, look.LookUpName
--, coalesce(r.amount, 0) - coalesce(p.amount, 0) as total
--, p.amount as p_amount
from fim_receives r 
inner join IcmBidderCollateral col on r.icm_po_con_col_seq = col.BidderColId 
left join IcmMasLookUp look on col.ColStatusId = look.LookUpId
left join IcmMasContact c on r.contact_seq = c.ConId
left join IcmPurchaseOrder po on col.BidderId = po.BidderId and po.RecordStatus in ('A', 'J')
left join (select quo.BidderId, sum(quo.NetAmount) as amount
	from IcmBidderQuotation quo
	group by quo.BidderId
	) bid on po.BidderId = bid.BidderId
left join (select p.pay_seq, p.amount, p.ref_no
, case when p.method_seq in (1, 3)
	then p.real_pay_date
	else case when p.method_seq = 2
		then chq.pay_date
		else pip.pip_date
		end
	end as pay_date
from fim_payments p
left join fim_payment_cheques pc on p.pay_seq = pc.pay_seq
left join fim_cheques chq on pc.cheque_seq = chq.cheque_seq
left join fim_payments_payinpack pip on p.pay_seq = pip.pay_seq
where p.record_status = 'A'
) p on r.pay_seq = p.pay_seq and p.pay_date is not null
where r.record_status not like 'D'
and r.receive_date between @start and @end

order by r.receive_date";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dtb;
        }

        public IList<M_FIM_MAS_Banks> GetList()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MAS_Banks> infoList = new List<M_FIM_MAS_Banks>();

            var dbManager = new DBM.DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "select bank.bank_seq,acc.bank_acc_seq,bank.bank_name, acc.bank_acc_no"
                    + ", CONCAT(acc.bank_acc_no,' ',bank.bank_name) as acc_name_no"
                    + " from fim_mas_bank_accounts acc"
                    + " inner join fim_mas_bank_branchs brch on acc.branch_seq = brch.branch_seq"
                    + " inner join fim_mas_banks bank on brch.bank_seq = bank.bank_seq"
                    + " where acc.record_status not like 'D' order by bank_name";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MAS_Banks info = new M_FIM_MAS_Banks();
                        info.bank_seq = Convert.ToInt32(drc[i]["Bank_acc_seq"]);
                        info.bank_name = drc[i]["Acc_name_no"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public bool chkBgmSourceAll(string bgmSource, string dbValue)
        {
            bool flag = false;

            if (bgmSource == "0")
            {
                flag = true;
            }
            else if (bgmSource == dbValue)
            {
                flag = true;
            }

            return flag;
        }

        public bool chkBank2All(string bank, string dbBank)
        {
            bool ans = false;

            if (bank == "0")
            {
                ans = true;
            }
            else if (bank == dbBank)
            {
                ans = true;
            }

            return ans;
        }

        public void printCover()
        {
            DateTime? dtStartDateRec = txtSumUpPaymentDateFrom.GetDateValue();
            DateTime? dtEndDateRec = txtSumUpPaymentDateTo.GetDateValue();
            string bgmSeqReceive = ddlBgmsource.SelectedValue.ToString();
            string bankAccSeq = ddlBank2.SelectedValue.ToString();

            if (dtStartDateRec == null || dtEndDateRec == null)
            {
                main.MessageAlert(Page, "กรุณากรอกวันที่ก่อนออกรายงาน");
                return;
            }

            DataTable dtbRec = new DataTable();
            double dSumRec = 0;

            Dictionary<string, string> wordFields = new Dictionary<string, string>();

            string qry = string.Format("select ROW_NUMBER()OVER(ORDER BY a.receive_seq DESC) AS Row,a.receive_date as date"
                + ",a.receipt_no as receiptNo,(case a.contact_seq when 0 then b.NameTh else a.temp_contact end) as ConName"
                + ",(b.TaxCode)as CodeCon,'-' as HQName,'-' as BranchName,(case a.vat_amount when 0 then 0 else a.amount-a.vat_amount end) as amount"
                + ",a.vat_amount as vatamount, a.amount as IncludeVat,(case a.vat_amount when  0   then a.amount  else 0 END) as a"
                + " from fim_receives a"
                //+ " left join fim_vat_dtls vd on a.pay_seq = vd.pay_seq"
                //+ " left join fim_vat v on vd.vat_seq = v.vat_seq and v.record_status <> 'D'"
                + " left join IcmMasContact b on a.contact_seq=b.ConId"
                + " left join (select bank.bank_seq,acc.bank_acc_seq,bank.bank_name"
                    + " from fim_mas_bank_accounts acc"
                    + " inner join fim_mas_bank_branchs brch on acc.branch_seq = brch.branch_seq"
                    + " inner join fim_mas_banks bank on brch.bank_seq = bank.bank_seq"
                    + ") as banks on a.bank_acc_seq = banks.bank_acc_seq"
                + " left join fim_mas_receive_types rt on a.receive_type_seq = rt.receive_type_seq"
                + " where a.record_status not in ('D', 'C')"// and vd.vat_seq is null and coalesce(a.vat_amount, 0) > 0"
                + " and rt.temp_income <> 1"
                + (bgmSeqReceive.Trim() == "" ? "" : " and a.bgm_source_seq = {0}")
                + " and a.receive_date >= '{1}' and a.receive_date <= '{2}'"
                + (bankAccSeq.Trim() == "" ? "" : " and banks.bank_acc_seq = {3}")
                , bgmSeqReceive
                , string.Format("{0}-{1}-{2}", dtStartDateRec.Value.Year > 2500 ? dtStartDateRec.Value.Year - 543 : dtStartDateRec.Value.Year, dtStartDateRec.Value.Month, dtStartDateRec.Value.Day)
                , string.Format("{0}-{1}-{2}", dtEndDateRec.Value.Year > 2500 ? dtEndDateRec.Value.Year - 543 : dtEndDateRec.Value.Year, dtEndDateRec.Value.Month, dtEndDateRec.Value.Day)
                , bankAccSeq);

            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                using (System.Data.SqlClient.SqlDataAdapter adp = new System.Data.SqlClient.SqlDataAdapter(qry, conn))
                {
                    adp.Fill(dtbRec);
                }
            }

            foreach (DataRow dr in dtbRec.Rows)
            {
                dSumRec += main.ConvertNullDouble(dr["IncludeVat"]) ?? 0;
            }

            wordFields.Add("Date", DateTime.Now.ToLongDateString());
            wordFields.Add("RageReciveDate", string.Format("วันที่ {0} - {1}", dtStartDateRec.Value.ToLongDateString(), dtEndDateRec.Value.ToLongDateString()));
            wordFields.Add("CompCode", "");
            wordFields.Add("Amount", dSumRec.ToString("#,##0.00"));
            wordFields.Add("Total", dSumRec.ToString("#,##0.00"));
            wordFields.Add("TotalThaiText", BahtText.ToBahtText(dSumRec));

            string strTemplatePath = Server.MapPath("~/Reports/Templates/รายงานใบปะหน้านำส่งเงิน.docx");
            string strSaveAsPath = Server.MapPath("~/Reports/Temp/");
            string strSaveAsFileName = string.Format("Cover{0}.docx", DateTime.Now.ToString("yyyyMMddHHmmssffff"));

            if (mailMerge.MergeTemplate(strTemplatePath, strSaveAsPath + strSaveAsFileName, wordFields, null, null, null))
            {
                Response.Clear();
                Response.ContentType = "text/plain";
                Response.AddHeader("Content-Disposition", "attachment; filename=" + strSaveAsFileName);
                Response.WriteFile(strSaveAsPath + strSaveAsFileName);
                Response.Flush();
                if (true)
                {
                    System.IO.File.Delete(strSaveAsPath + strSaveAsFileName);
                }
                Response.End();
            }
        }

        public decimal setValueNumber(decimal value)
        {
            string[] strPre = value.ToString().Split('.');

            if (strPre.Length > 1 && strPre[1].Length > 2)
            {
                strPre[1] = strPre[1].Substring(0, 2);
                value = Convert.ToDecimal(strPre[0] + "." + strPre[1]);
            }

            return value;
        }

        public DataTable VatBuy()
        {
            string strBgmSourceSeq = ddlVatBgmSourceSeq.SelectedValue;
            DataTable dt = new DataTable();
            var dbManager = new DBM.DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            int engYear = int.Parse(txtYear.Text) - 543;
            int month = int.Parse(ddlMonth.SelectedValue);
            DateTime dtFrom = new DateTime(engYear, month, 1);
            DateTime dtTo = dtFrom.AddMonths(1).AddDays(-1);
            int lastDate = dtTo.Day;
            string startDate = string.Format("{0}-{1}-{2}", engYear, month, 1);
            string endDate = string.Format("{0}-{1}-{2}", engYear, month, lastDate);

            string strSQL = @"
select ROW_NUMBER () OVER (ORDER BY real_pay_date, ref_no) as Row, t.real_pay_date, t.ref_no
, t.ConName, t.TaxConCode, t.branch_no, t.amountExCludeVat, t.vat, t.amount
, t.pay_ref_no, t.remarks
from (
SELECT p.pay_seq as seq
, case when mrd.ref_date is not NULL
	then mrd.ref_date
	else case when p.method_seq in (1,3) 
		then p.real_pay_date
		else case when p.method_seq = 2 
			then cheq.cheq_pay_date
			else case when p.method_seq = 4 
				then pip.pip_date
				else null 
			end 
		end 
	end
	end as real_pay_date
, case when mrd.ref_no is not null and len(mrd.ref_no) > 0 then mrd.ref_no else p.ref_no end as ref_no
, case when mrd.NameTh is not null then mrd.NameTh
	else case when p.contact_seq is not null then b.NameTh
		else concat(np.prefix_short, emp.first_name, ' ', last_name)
		end
	end as ConName
, case when mrd.TaxCode is not null then mrd.TaxCode
	else case when p.contact_seq is not null then b.TaxCode
		else emp.citizen
		end
	end as TaxConCode
, mrd.branch_no
, COALESCE(mrd.amount, 0) - COALESCE(mrd.amount_vat, 0) as amountExCludeVat
, COALESCE(mrd.amount_vat, 0) as vat
, COALESCE(mrd.amount, 0) as amount
, p.ref_no as pay_ref_no, p.remarks
FROM fim_vat v
inner join fim_vat_dtls vd on v.vat_seq = vd.vat_seq
inner JOIN fim_payments p on vd.pay_seq = p.pay_seq
inner JOIN fim_money_requests mr ON p.money_req_seq = mr.money_req_seq
inner JOIN (select mrd.money_req_seq
	, sum(coalesce(mrd.amount, 0)) as amount
	, sum(coalesce(mrd.amount_vat, 0)) as amount_vat
	, c.NameTh, mrd.branch_no, mrd.ref_no, mrd.ref_date, c.TaxCode
	from fim_money_request_dtls mrd
	left join IcmMasContact c on mrd.ConId = c.ConId
	where coalesce(mrd.amount_vat, 0) > 0
	and mrd.record_status <> 'D'
	group by mrd.money_req_seq, c.NameTh, mrd.branch_no, mrd.ref_no, mrd.ref_date, c.TaxCode
) mrd ON mr.money_req_seq = mrd.money_req_seq
LEFT JOIN IcmMasContact b ON p.contact_seq = b.ConId
LEFT JOIN hrm_ps_employees emp ON p.emp_seq = emp.emp_seq
LEFT JOIN mas_name_prefixes np ON emp.name_prefix_seq = np.name_prefix_seq
LEFT JOIN (SELECT pcheq.pay_seq, min(cheq.pay_date) AS cheq_pay_date
	, cheq.cheque_no, cheq.receiver, cheq.remarks, cheq.cheque_seq
	FROM fim_payment_cheques pcheq
	INNER JOIN fim_cheques cheq ON pcheq.cheque_seq = cheq.cheque_seq
	WHERE pcheq.record_status <> 'D'
	AND cheq.record_status NOT IN ('D', 'C')
	GROUP BY pcheq.pay_seq, cheq.pay_date, cheq.cheque_no, cheq.receiver, cheq.remarks, cheq.cheque_seq
) cheq ON p.pay_seq = cheq.pay_seq
LEFT JOIN fim_payments_payinpack pip on p.pay_seq = pip.pay_seq
WHERE v.record_status <> 'D'
and v.year_no = @year_no
and v.month_no = @month_no"
                + (string.IsNullOrEmpty(strBgmSourceSeq) ? "" : " and p.bgm_source_seq = @bgm_source_seq")

                + @"
union all

SELECT r.return_seq as seq
, case when rd.ref_date is not NULL
	then rd.ref_date
	else r.return_date
	end as real_pay_date
, case when rd.ref_no is not null and len(rd.ref_no) > 0 then rd.ref_no else r.ref_no end as ref_no
, case when rd.NameTh is not null then rd.NameTh
	else case when mr.contact_seq is not null then b.NameTh
		else concat(np.prefix_short, emp.first_name, ' ', last_name)
		end
	end as ConName
, case when rd.TaxCode is not null then rd.TaxCode
	else case when mr.contact_seq is not null then b.TaxCode
		else emp.citizen
		end
	end as TaxConCode
, rd.branch_no
, COALESCE(rd.amount, 0) - COALESCE(rd.amount_vat, 0) as amountExCludeVat
, COALESCE(rd.amount_vat, 0) as vat
, COALESCE(rd.amount, 0) as amount
, r.ref_no as pay_ref_no, r.remarks
FROM fim_money_returns r
inner join fim_money_requests mr on r.money_req_seq = mr.money_req_seq
inner JOIN (select rd.return_seq
	, sum(coalesce(rd.amount, 0)) as amount
	, sum(coalesce(rd.vat_amount, 0)) as amount_vat
	, c.NameTh, rd.branch_no, rd.ref_no, rd.ref_date, c.TaxCode
	from fim_money_return_dtls rd
    inner join fim_vat_dtls vd on rd.return_dtls_seq = vd.return_dtls_seq
    inner join fim_vat v on vd.vat_seq = v.vat_seq
	left join IcmMasContact c on rd.ConId = c.ConId
	WHERE v.record_status <> 'D'
    and v.year_no = @year_no
    and v.month_no = @month_no
	group by rd.return_seq, c.NameTh, rd.branch_no, rd.ref_no, rd.ref_date, c.TaxCode
) rd ON r.return_seq = rd.return_seq
LEFT JOIN IcmMasContact b ON mr.contact_seq = b.ConId
LEFT JOIN hrm_ps_employees emp ON mr.emp_seq = emp.emp_seq
LEFT JOIN mas_name_prefixes np ON emp.name_prefix_seq = np.name_prefix_seq
where 0=0"
                + (string.IsNullOrEmpty(strBgmSourceSeq) ? "" : " and mr.bgm_source_seq = @bgm_source_seq")
                + @"
) t

order by real_pay_date, pay_ref_no
";

            //where real_pay_date >= @dtFrom
            //and real_pay_date <= @dtTo
            //and real_pay_date is not null

            dbManager.Open();
            dbManager.CreateParameters(3);
            dbManager.AddParameters(0, "@year_no", txtYear.Text);
            dbManager.AddParameters(1, "@month_no", ddlMonth.SelectedValue);
            dbManager.AddParameters(2, "@bgm_source_seq", strBgmSourceSeq);

            dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            dbManager.Close();

            return dt;

        }

        protected void rblPaymentChequeType_SelectedIndexChanged(object sender, EventArgs e)
        {
            PanelPaymentCheque1.Visible = rblPaymentChequeType.SelectedValue == "1";
            PanelPaymentCheque2.Visible = rblPaymentChequeType.SelectedValue == "2";
        }

        public void RemainMoneyRequest()
        {
            string strBgmSourceSeq = ddlBgmSourceSeq3.SelectedValue;
            string strBgmYear = txtBgmYear3.Text;
            DataTable dt = new DataTable();
            var dbManager = new DBM.DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            string strSQL = @"
select mr.ref_no, mr.money_req_date, mr.money_req_name, mrd.amount
from fim_money_requests mr
left join (select money_req_seq, sum(coalesce(amount, 0)) as amount
	from fim_money_request_dtls
	where record_status <> 'D'
	group by money_req_seq
	) mrd on mr.money_req_seq = mrd.money_req_seq
left join fim_payments p on mr.money_req_seq = p.money_req_seq
	and p.record_status <> 'D'
where mr.record_status = 'A'
and p.pay_seq is null"
                + (!string.IsNullOrEmpty(strBgmYear) ? " and mr.bgm_year = @bgm_year" : "")
                + (!string.IsNullOrEmpty(strBgmSourceSeq) ? " and mr.bgm_source_seq = @bgm_source_seq" : "")
+ " order by mr.ref_no";

            dbManager.Open();
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@bgm_source_seq", strBgmSourceSeq);
            dbManager.AddParameters(1, "@bgm_year", strBgmYear);

            dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            dbManager.Close();

            Dictionary<string, DataTable> dicDtb = new Dictionary<string, DataTable>();
            Dictionary<string, string> dic = new Dictionary<string, string>();

            dicDtb.Add("A3", dt);
            dic.Add("A1", "ณ วันที่ " + DateTime.Today.ToLongDateString());

            mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/รายงานรายการขอเบิกที่ยังไม่ทำรายการจ่ายเงิน.xlsx"), string.Format("RQNoPQ-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), dicDtb, dic);
        }

        public void GetReceiveSummary(DateTime dtDateStart, DateTime dtDateEnd)
        {
            DataTable dt = new DataTable();
            var dbManager = new DBM.DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            string strSQL = @"
select count (*) as row_count
, (select count(*) as row_count
	from fim_receives
	where record_status = 'C'
	and bgm_source_seq = 11
	and receive_mode = 2
	and receive_date >= @date_start
	and receive_date <= @date_end) as cancel_count
, sum(r.amount) as amount
, sum(case when r.method_seq = 1
	then case when b2.bank_seq = 6
		then r.amount
		else 0
		end
	else case when b.bank_seq = 6
		then r.amount
		else 0
		end
	end) as ktb
, sum(case when r.method_seq = 1
	then case when b2.bank_seq = 14
		then r.amount
		else 0
		end
	else case when b.bank_seq = 14
		then r.amount
		else 0
		end
	end) as scb
, sum(case when r.method_seq = 1 and t.transfer_seq is null
	then r.amount
	else 0
	end) as cash
from fim_receives r
left join fim_mas_bank_accounts acc on r.bank_acc_seq = acc.bank_acc_seq
left join fim_mas_bank_branchs b on acc.branch_seq = b.branch_seq
left join fim_transfers t on r.deposit_seq = t.transfer_seq
	and t.transfer_date >= @date_start
	and t.transfer_date <= @date_end
left join fim_mas_bank_accounts acc2 on t.to_bank_acc_seq = acc2.bank_acc_seq
left join fim_mas_bank_branchs b2 on acc2.branch_seq = b2.branch_seq
where r.record_status not in ('D', 'C')
and r.bgm_source_seq = 11
and r.receive_mode = 2
and r.receive_date >= @date_start
and r.receive_date <= @date_end";

            dbManager.Open();
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@date_start", dtDateStart);
            dbManager.AddParameters(1, "@date_end", dtDateEnd);

            dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            dbManager.Close();

            if (dt.Rows.Count > 0)
            {
                var nCount = (main.ConvertNullInt(dt.Rows[0]["row_count"]) ?? 0).ToString("#,##0 ใบ");
                var nCancleCount = (main.ConvertNullInt(dt.Rows[0]["cancel_count"]) ?? 0).ToString("#,##0 ใบ");
                var dSumAmount = (main.ConvertNullDouble(dt.Rows[0]["amount"]) ?? 0).ToString("#,##0.00 บาท");
                var dKTBAmount = (main.ConvertNullDouble(dt.Rows[0]["ktb"]) ?? 0).ToString("#,##0.00 บาท");
                var dSCBAmount = (main.ConvertNullDouble(dt.Rows[0]["scb"]) ?? 0).ToString("#,##0.00 บาท");
                var dCashAmount = (main.ConvertNullDouble(dt.Rows[0]["cash"]) ?? 0).ToString("#,##0.00 บาท");

                Dictionary<string, string> dicReceiveSummary = new Dictionary<string, string>();
                dicReceiveSummary.Add("A2", "ตั้งแต่วันที่ " + dtDateStart.ToLongDateString() + " ถึงวันที่ " + dtDateEnd.ToLongDateString());
                dicReceiveSummary.Add("C5", nCount);
                dicReceiveSummary.Add("C6", nCancleCount);
                dicReceiveSummary.Add("C7", dSumAmount);
                dicReceiveSummary.Add("C8", dKTBAmount);
                dicReceiveSummary.Add("C9", dSCBAmount);
                dicReceiveSummary.Add("C10", dCashAmount);

                mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/ใบสรุปปะหน้านำส่งเงินรายได้.xlsx"), string.Format("ReceiveSummary-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), null, dicReceiveSummary);
            }
            else
            {
                main.MessageAlert(Page, "Report error.");
            }
        }

        public DataTable GetTax1(string strYear)
        {
            var dbManager = new DBM.DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            dbManager.Open();
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@year", strYear);

            string strSQL = @"
select e.citizen as CitizenId
, concat(np.prefix_name, e.first_name, ' ', e.last_name) as EmpName
, case when ad2.address_seq is not null
	then concat(coalesce(ad2.address_descr, ''), ' ', coalesce(d2.district_name, ''), ' ', coalesce(a2.amp_name, ''), ' ', coalesce(p2.prov_name, ''))
	else concat(coalesce(ad1.address_descr, ''), ' ', coalesce(d1.district_name, ''), ' ', coalesce(a1.amp_name, ''), ' ', coalesce(p1.prov_name, ''))
	end as Address
, case when DATEPART(year, e.date_work_start) = 2558 - 543 then e.date_work_start else null end as StartDate
--, case when e.mar_stat_seq in (2, 3) then 1 else 0 end as IsMarried
, 0 as IsMarried
, 0 as StudyChildCount
, 0 as NonStudyChildCount
, 0 as OtherDeduct
, prd.salary as SumSalary
, prd.tax as SumTax
from hrm_ps_employees e
inner join (select emp_seq, sum(coalesce(salary, 0)) as salary, sum(coalesce(tax, 0)) as tax
	from fim_payroll_dtls
	where [year] = @year
	group by emp_seq
	) prd on e.emp_seq = prd.emp_seq
left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq
--left join (select top 1 * from hrm_ps_emp_address where address_type_seq = 1 and record_status <> 'D' order by address_seq desc) ad1 on e.emp_seq = ad1.emp_seq
--left join (select top 1 * from hrm_ps_emp_address where address_type_seq = 2 and record_status <> 'D' order by address_seq desc) ad2 on e.emp_seq = ad2.emp_seq
left join hrm_ps_emp_address ad1 on e.emp_seq = ad1.emp_seq and ad1.record_status <> 'D' and ad1.address_type_seq = 1
left join hrm_ps_emp_address ad2 on e.emp_seq = ad2.emp_seq and ad2.record_status <> 'D' and ad2.address_type_seq = 2
left join mas_districts d1 on ad1.district_seq = d1.district_seq
left join mas_districts d2 on ad2.district_seq = d2.district_seq
left join mas_amphurs a1 on d1.amp_seq = a1.amp_seq
left join mas_amphurs a2 on d2.amp_seq = a2.amp_seq
left join mas_provinces p1 on a1.prov_seq = p1.prov_seq
left join mas_provinces p2 on a2.prov_seq = p2.prov_seq
--left join mas_marital_statuses ms on e.mar_stat_seq = ms.mar_stat_seq
order by e.first_name, e.last_name
";

            try
            {
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                return dtb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

//        public DataTable GetTax2(string strYear, )
//        {
//            var dbManager = new DBM.DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
//            dbManager.Open();
//            dbManager.CreateParameters(1);
//            dbManager.AddParameters(0, "@year", strYear);

//            string strSQL = @"
//select e.citizen as CitizenId
//, concat(np.prefix_name, e.first_name, ' ', e.last_name) as EmpName
//, case when ad2.address_seq is not null
//	then concat(coalesce(ad2.address_descr, ''), ' ', coalesce(d2.district_name, ''), ' ', coalesce(a2.amp_name, ''), ' ', coalesce(p2.prov_name, ''))
//	else concat(coalesce(ad1.address_descr, ''), ' ', coalesce(d1.district_name, ''), ' ', coalesce(a1.amp_name, ''), ' ', coalesce(p1.prov_name, ''))
//	end as Address
//, case when DATEPART(year, e.date_work_start) = 2558 - 543 then e.date_work_start else null end as StartDate
//--, case when e.mar_stat_seq in (2, 3) then 1 else 0 end as IsMarried
//, 0 as IsMarried
//, 0 as StudyChildCount
//, 0 as NonStudyChildCount
//, 0 as OtherDeduct
//, prd.salary as SumSalary
//, prd.tax as SumTax
//from hrm_ps_employees e
//inner join (select emp_seq, sum(coalesce(salary, 0)) as salary, sum(coalesce(tax, 0)) as tax
//	from fim_payroll_dtls
//	where [year] = @year
//	group by emp_seq
//	) prd on e.emp_seq = prd.emp_seq
//left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq
//--left join (select top 1 * from hrm_ps_emp_address where address_type_seq = 1 and record_status <> 'D' order by address_seq desc) ad1 on e.emp_seq = ad1.emp_seq
//--left join (select top 1 * from hrm_ps_emp_address where address_type_seq = 2 and record_status <> 'D' order by address_seq desc) ad2 on e.emp_seq = ad2.emp_seq
//left join hrm_ps_emp_address ad1 on e.emp_seq = ad1.emp_seq and ad1.record_status <> 'D' and ad1.address_type_seq = 1
//left join hrm_ps_emp_address ad2 on e.emp_seq = ad2.emp_seq and ad2.record_status <> 'D' and ad2.address_type_seq = 2
//left join mas_districts d1 on ad1.district_seq = d1.district_seq
//left join mas_districts d2 on ad2.district_seq = d2.district_seq
//left join mas_amphurs a1 on d1.amp_seq = a1.amp_seq
//left join mas_amphurs a2 on d2.amp_seq = a2.amp_seq
//left join mas_provinces p1 on a1.prov_seq = p1.prov_seq
//left join mas_provinces p2 on a2.prov_seq = p2.prov_seq
//--left join mas_marital_statuses ms on e.mar_stat_seq = ms.mar_stat_seq
//order by e.first_name, e.last_name
//";

//            try
//            {
//                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

//                return dtb;
//            }
//            catch (Exception ex)
//            {
//                throw ex;
//            }
//            finally
//            {
//                dbManager.Dispose();
//            }
//        }
    }
}